USE master
GO

--create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ssbrouter';
GO

--SSBRouter's certificate
-- Create a certificate to authenticate the endpoint.
IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'TrpCert_SSBRouter')
 DROP CERTIFICATE TrpCert_SSBRouter;
GO

/**create transport certificate**/
CREATE CERTIFICATE TrpCert_SSBRouter
 WITH SUBJECT = 'Service broker transport authentication for SSBRouter',
 START_DATE = '01/01/2012',
 EXPIRY_DATE = '01/01/2022';
GO

-- Backup to a file to allow the certificate to be given to the initiator.
BACKUP CERTIFICATE TrpCert_SSBRouter
 TO FILE = 'd:\SSBCertificates\TrpCert_SSBRouter.cer';

GO

--CREATE CERTIFICATE TrpCert_SSBRouter
--	AUTHORIZATION dbo
--    FROM FILE = 'D:\SSBCertificates\TrpCert_Center.cer' 
--    WITH PRIVATE KEY (FILE = 'D:\SSBCertificates\TrpCert_Center.pvk', 
--    DECRYPTION BY PASSWORD = 'ssbrouter');
--GO

--Create a target login and user for web001
CREATE LOGIN Web001Login WITH PASSWORD = 'ssbrouter'
CREATE USER Web001Dbo FOR LOGIN Web001Login 

IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'TrpCert_Web001')
 DROP CERTIFICATE TrpCert_Web001;
GO

CREATE CERTIFICATE TrpCert_Web001
	AUTHORIZATION Web001Dbo 
    FROM FILE = 'D:\SSBCertificates\TrpCert_Web001.cer' 
GO

--Warehouse-001's certificate
CREATE LOGIN WMS001Login WITH PASSWORD = 'ssbrouter'
CREATE USER WMS001Dbo FOR LOGIN WMS001Login

CREATE CERTIFICATE TrpCert_WMS001
	AUTHORIZATION WMS001Dbo
    FROM FILE = 'D:\SSBCertificates\TrpCert_WMS001.cer' 
GO

--Warehouse-002's certificate
--CREATE LOGIN WMS002Dbo WITH PASSWORD = 'ssbrouter'
--CREATE USER WMS002Dbo FOR LOGIN WMS002Dbo

--CREATE CERTIFICATE TrpCert_WH52
--	AUTHORIZATION WMS002Dbo
--    FROM FILE = 'D:\SSBCertificates\TrpCert_WMS002.cer' 
--GO

--create ssb endpoint
SELECT * FROM sys.endpoints WHERE type_desc='SERVICE_BROKER';
CREATE ENDPOINT EP_SSBRouter
	STATE = STARTED
	AS TCP
	(
		LISTENER_PORT = 4050
	)
	FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE TrpCert_SSBRouter)

--grant permission
SELECT * FROM sys.service_broker_endpoints

GRANT CONNECT ON ENDPOINT::EP_SSBRouter to Web001Login
GRANT CONNECT ON ENDPOINT::EP_SSBRouter to WMS001Login
--GRANT CONNECT ON ENDPOINT::EP_SSBRouter to WMS002Login

GO






